package fact.view.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.ViewScoped;
import javax.sql.DataSource;

import fact.view.DTO.NodeAttributeDTO;


@ManagedBean(name = "nodeAttributeModelBean")
@ViewScoped
public class NodeAttributeModelBean {

	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<NodeAttributeDTO> getAtributeList(int nodeId) throws SQLException
	{
		ArrayList<NodeAttributeDTO> attributeList=new ArrayList<NodeAttributeDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select ns.*,nh.LABEL node_name,MST_MAKES.MAKE_NAME,MST_SCENARIO.SCENARIO_NAME from NODE_SCENARIO_ATTRIBUTES ns ,MST_MAKES,MST_SCENARIO,node_hierarchy nh " +
				"Where ns.MAKE_ID=MST_MAKES.MAKE_ID " +
				"And   ns.SCENARIO_ID=MST_SCENARIO.SCENARIO_ID " +
				"And   ns.SCENARIO_ID=MST_SCENARIO.SCENARIO_ID " +
				"and    nh.NODE_ID=ns.NODE_ID "+
				"And   ns.NODE_ID=? " +
				"ORDER BY ns.make_id,MST_SCENARIO.SCENARIO_ID ");
		
		ps.setInt(1, nodeId);
		ResultSet result =  ps.executeQuery();
		
		NodeAttributeDTO na=null;
				while(result.next())
				{
					na=new NodeAttributeDTO();
					na.setMakeId(result.getInt("MAKE_ID"));
					na.setNodeId(result.getInt("NODE_ID"));
					na.setNodeName(result.getString("node_name"));
					na.setScenarioid(result.getInt("SCENARIO_ID"));
					na.setMakeName(result.getString("MAKE_NAME"));
					na.setScenarioName(result.getString("SCENARIO_NAME"));
					na.setMinValue(result.getInt("MIN_VALUE"));
					na.setMaxValue(result.getInt("MAX_VALUE"));
					na.setVisibilityFlag(result.getInt("VISIBILITY_FLAG"));					
					
					attributeList.add(na);
				}
		
		
		return attributeList;
	}
	
	public NodeAttributeDTO getAttribute(int makeId,int nodeId,int scenarioId) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Select ns.*,MST_MAKES.MAKE_NAME,MST_SCENARIO.SCENARIO_NAME from NODE_SCENARIO_ATTRIBUTES ns ,MST_MAKES,MST_SCENARIO " +
				"Where ns.MAKE_ID=MST_MAKES.MAKE_ID " +
				"And   ns.SCENARIO_ID=MST_SCENARIO.SCENARIO_ID " +
				"And   ns.SCENARIO_ID=MST_SCENARIO.SCENARIO_ID " +
				"And   ns.NODE_ID=? And ns.MAKE_ID=? And ns.SCENARIO_ID=? " +
				"ORDER BY MST_SCENARIO.POSITION_NO ");
		
		ps.setInt(1, nodeId);
		ResultSet result =  ps.executeQuery();
		NodeAttributeDTO na=null;
				if(result.next())
				{
					na=new NodeAttributeDTO();
					na=new NodeAttributeDTO();
					na.setMakeId(result.getInt("MAKE_ID"));
					na.setNodeId(result.getInt("NODE_ID"));
					na.setScenarioid(result.getInt("SCENARIO_ID"));
					na.setMakeName(result.getString("MAKE_NAME"));
					na.setScenarioName(result.getString("SCENARIO_NAME"));
					na.setMinValue(result.getInt("MIN_VALUE"));
					na.setMaxValue(result.getInt("MAX_VALUE"));
					na.setVisibilityFlag(result.getInt("VISIBILITY"));	
				}
		
		return na;
	}
	
	
	public boolean editNode(NodeAttributeDTO attribute) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps = con.prepareStatement("Update NODE_SCENARIO_ATTRIBUTES Set Max_Value=?, Min_Value=?, Visibility_Flag=? Where Make_Id=? and Node_Id=? and Scenario_id=?");  
		ps.setInt(1, attribute.getMaxValue());
		ps.setInt(2, attribute.getMinValue());
		ps.setInt(3, attribute.getVisibilityFlag());
		ps.setInt(3, attribute.getMakeId());
		ps.setInt(3, attribute.getNodeId());
		ps.setInt(3, attribute.getScenarioid());
		
		int result =  ps.executeUpdate();
		
		if(result==1)
			return true;
		else
			return false;
		
	}
	
	public boolean editNodes(ArrayList<NodeAttributeDTO> attributeList) throws SQLException
	{
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");
 
		PreparedStatement ps =null;
		int result=0;
		
		for(int i=0;i<attributeList.size();i++)
		{
		ps = con.prepareStatement("Update NODE_SCENARIO_ATTRIBUTES Set Max_Value=?, Min_Value=?, Visibility_Flag=? Where Make_Id=? and Node_Id=? and Scenario_id=?");  
		ps.setInt(1, attributeList.get(i).getMaxValue());
		ps.setInt(2, attributeList.get(i).getMinValue());
		ps.setInt(3, attributeList.get(i).getVisibilityFlag());
		ps.setInt(4, attributeList.get(i).getMakeId());
		ps.setInt(5, attributeList.get(i).getNodeId());
		ps.setInt(6, attributeList.get(i).getScenarioid());
		
		result +=  ps.executeUpdate();
		}
		
		if(result==attributeList.size())
			return true;
		else
			return false;
		
	}
	
	
}
